using System;
using System.Collections.Generic;
using System.Text;
using SqlTools.CodeGenerator.Data;
using System.IO;

namespace SqlTools.CodeGenerator.SQL
{
	public class StoredProcedureGenerator
	{
		protected Table _table;

		public StoredProcedureGenerator(Table table)
		{
			_table = table;
		}

		public void CreateStoredProcedures()
		{
			StringBuilder sb = new StringBuilder();

			foreach ( Method method in _table.Methods )
			{
				switch ( method.Type )
				{
                        //TODOS GCP add SelectAll
					case MethodType.SelectSingle:
					case MethodType.SelectMultiple:
						GenerateSelect(sb, method);
						sb.AppendLine().AppendLine();
						break;
					case MethodType.Insert:
						GenerateInsert(sb, method);
						sb.AppendLine().AppendLine();
						break;
					case MethodType.Update:
						GenerateUpdate(sb, method);
						sb.AppendLine().AppendLine();
						break;
					case MethodType.Delete:
						GenerateDelete(sb, method);
						sb.AppendLine().AppendLine();
						break;
				}
			}

			string fileText = sb.ToString();

			string fileName = Path.Combine(_table.Project.RootDirectory, _table.SQLDirectory);

			Directory.CreateDirectory(fileName);

			fileName = Path.Combine(fileName, _table.TableName + "_Procs.sql");

			StreamWriter sw = File.CreateText(fileName);
			sw.Write(fileText);
			sw.Close();
		}

		protected void GenerateSelect(StringBuilder sb, Method method)
		{
			CreateHeader(sb);

			GenerateDropStatement(sb, method.StoredProcedureName);

			sb.Append("CREATE PROCEDURE [dbo].[").Append(method.StoredProcedureName).AppendLine("]");
            bool first = true; 
            if (method.Parameters.Count > 0)
            {
                sb.AppendLine("\t(");



                //TODOS GPC
                if (method.Parameters[0].Property == null) return;
                if (method.Parameters.Count == 1 && method.Parameters[0].Property.Type == TablePropertyType.PrimaryKey)
                {
                    PrimaryKeyTableProperty pkprop = _table.GetPrimaryKey();
                    for (int i = 0; i < pkprop.Properties.Count; i++)
                    {
                        if (!first)
                        {
                            sb.AppendLine(",");
                        }
                        else
                        {
                            first = false;
                        }

                        TableProperty prop = pkprop.Properties[i];

                        sb.Append("\t");
                        CreateParameter(sb, prop, false, false);
                    }
                    sb.AppendLine();
                }
                else
                {
                    for (int i = 0; i < method.Parameters.Count; i++)
                    {
                        if (!first)
                        {
                            sb.AppendLine(",");
                        }
                        else
                        {
                            first = false;
                        }

                        MethodParameter param = method.Parameters[i];
                        // loop through primary
                        sb.Append("\t");
                        CreateParameter(sb, param.Property, false, false);
                    }
                    sb.AppendLine();
                }
                sb.AppendLine("\t)"); 
            }
			sb.AppendLine("AS");
			sb.Append("SELECT * FROM [").Append(_table.TableName).AppendLine("]");
            if (method.Parameters.Count>0)
            {
                sb.AppendLine("WHERE");

                first = true;
                if (method.Parameters.Count == 1 && method.Parameters[0].Property.Type == TablePropertyType.PrimaryKey)
                {
                    PrimaryKeyTableProperty pkprop = _table.GetPrimaryKey();

                    first = true;
                    for (int i = 0; i < pkprop.Properties.Count; i++)
                    {
                        if (!first)
                        {
                            sb.AppendLine(" AND ");
                        }
                        else
                        {
                            first = false;
                        }

                        TableProperty prop = pkprop.Properties[i];

                        sb.Append("\t[").Append(prop.ColumnName).Append("] = ").Append("@").Append(prop.ColumnName);
                    }
                    sb.AppendLine();
                }
                else
                {
                    for (int i = 0; i < method.Parameters.Count; i++)
                    {
                        if (!first)
                        {
                            sb.AppendLine(" AND ");
                        }
                        else
                        {
                            first = false;
                        }

                        MethodParameter param = method.Parameters[i];
                        // loop through primary
                        sb.Append("\t");
                        sb.Append("\t[").Append(param.Property.ColumnName).Append("] = ").Append("@").Append(param.Property.ColumnName);
                    }
                    sb.AppendLine();
                } 
            }
		}

		protected void GenerateInsert(StringBuilder sb, Method method)
		{
			CreateHeader(sb);

			GenerateDropStatement(sb, method.StoredProcedureName);

			sb.Append("CREATE PROCEDURE [dbo].[").Append(method.StoredProcedureName).AppendLine("]");
			sb.AppendLine("\t(");

			bool first = true;
			for ( int i = 0; i < _table.Properties.Count; i++ )
			{
				TableProperty prop = _table.Properties[i];

				if ( prop.Type == TablePropertyType.PrimaryKey )
				{
					PrimaryKeyTableProperty pkprop = prop as PrimaryKeyTableProperty;
					for ( int j = 0; j < pkprop.Properties.Count; j++ )
					{
						if ( !first )
						{
							sb.AppendLine(",");
						}
						else
						{
							first = false;
						}

						TableProperty sprop = pkprop.Properties[j];
						sb.Append("\t");
						CreateParameter(sb, sprop, sprop.AutoGeneratedOnInsert, false);
					}
				}
				else
				{
					if ( !first )
					{
						sb.AppendLine(",");
					}
					else
					{
						first = false;
					}

					sb.Append("\t");
					CreateParameter(sb, prop, false, prop.Nullable);
				}

			}
			sb.AppendLine();
			sb.AppendLine("\t)");
			sb.AppendLine("AS");

			sb.Append("INSERT INTO [").Append(_table.TableName).AppendLine("] (");

			first = true;
			for ( int i = 0; i < _table.Properties.Count; i++ )
			{
				TableProperty prop = _table.Properties[i];

				if ( prop.Type == TablePropertyType.PrimaryKey )
				{
					PrimaryKeyTableProperty pkprop = prop as PrimaryKeyTableProperty;

					for ( int j = 0; j < pkprop.Properties.Count; j++ )
					{
						TableProperty sprop = pkprop.Properties[j];
						if ( sprop.AutoGeneratedOnInsert )
							continue;

						if ( !first )
						{
							sb.AppendLine(",");
						}
						else
						{
							first = false;
						}

						sb.Append("\t[");
						sb.Append(sprop.ColumnName);
						sb.Append("]");
					}
				}
				else
				{
					if ( !first )
					{
						sb.AppendLine(",");
					}
					else
					{
						first = false;
					}
					sb.Append("\t");
					sb.Append("[").Append(prop.ColumnName).Append("]");
				}
			}
			sb.AppendLine();

			sb.AppendLine(")");
			sb.AppendLine("VALUES (");

			first = true;
			for ( int i = 0; i < _table.Properties.Count; i++ )
			{
				TableProperty prop = _table.Properties[i];

				if ( prop.Type == TablePropertyType.PrimaryKey )
				{
					PrimaryKeyTableProperty pkprop = prop as PrimaryKeyTableProperty;
					for ( int j = 0; j < pkprop.Properties.Count; j++ )
					{
						TableProperty sprop = pkprop.Properties[j];
						if ( sprop.AutoGeneratedOnInsert )
							continue;

						if ( !first )
						{
							sb.AppendLine(",");
						}
						else
						{
							first = false;
						}

						sb.Append("\t@");
						sb.Append(sprop.ColumnName);

					}
				}
				else
				{
					if ( !first )
					{
						sb.AppendLine(",");
					}
					else
					{
						first = false;
					}

					sb.Append("\t@");
					sb.Append(prop.ColumnName);

				}
			}
			sb.AppendLine();

			sb.AppendLine(")");
			sb.AppendLine();
			PrimaryKeyTableProperty pk = _table.GetPrimaryKey();

			foreach ( TableProperty prop in pk.Properties )
			{
				if ( prop.AutoGeneratedOnInsert )
				{
					sb.Append("SET @").Append(prop.ColumnName).AppendLine(" = SCOPE_IDENTITY()");
					break;
				}
			}
		}

		protected void GenerateUpdate(StringBuilder sb, Method method)
		{
			// check for tables with only PK params
			// those should not have update methods

			if ( _table.Properties.Count == 1 && _table.Properties[0].Type == TablePropertyType.PrimaryKey )
				return;

			CreateHeader(sb);

			GenerateDropStatement(sb, method.StoredProcedureName);

			sb.Append("CREATE PROCEDURE [dbo].[").Append(method.StoredProcedureName).AppendLine("]");
			sb.AppendLine("\t(");

			bool first = true;
			for ( int i = 0; i < _table.Properties.Count; i++ )
			{
				TableProperty prop = _table.Properties[i];

				if ( prop.Type == TablePropertyType.PrimaryKey )
				{
					PrimaryKeyTableProperty pkprop = prop as PrimaryKeyTableProperty;
					for ( int j = 0; j < pkprop.Properties.Count; j++ )
					{
						if ( !first )
						{
							sb.AppendLine(",");
						}
						else
						{
							first = false;
						}

						TableProperty sprop = pkprop.Properties[j];
						sb.Append("\t");
						CreateParameter(sb, sprop, false, false);
					}
				}
				else
				{
					if ( !first )
					{
						sb.AppendLine(",");
					}
					else
					{
						first = false;
					}

					sb.Append("\t");
					CreateParameter(sb, prop, false, prop.Nullable);
				}
			}
			sb.AppendLine();

			sb.AppendLine("\t)");
			sb.AppendLine("AS");

			sb.Append("UPDATE [").Append(_table.TableName).AppendLine("] SET");

			first = true;
			for ( int i = 0; i < _table.Properties.Count; i++ )
			{
				TableProperty prop = _table.Properties[i];

				if ( prop.Type != TablePropertyType.PrimaryKey )
				{
					if ( !first )
					{
						sb.AppendLine(",");
					}
					else
					{
						first = false;
					}

					sb.Append("\t[");
					sb.Append(prop.ColumnName);
					sb.Append("] = @");
					sb.Append(prop.ColumnName);

				}
			}
			sb.AppendLine();

			sb.AppendLine("WHERE");

			PrimaryKeyTableProperty pk = _table.GetPrimaryKey();

			first = true;
			for ( int i = 0; i < pk.Properties.Count; i++ )
			{
				if ( !first )
				{
					sb.AppendLine(" AND ");
				}
				else
				{
					first = false;
				}

				TableProperty prop = pk.Properties[i];

				sb.Append("\t[").Append(prop.ColumnName).Append("] = ").Append("@").Append(prop.ColumnName);
			}
			sb.AppendLine();
		}

		protected void GenerateDelete(StringBuilder sb, Method method)
		{
			CreateHeader(sb);

			GenerateDropStatement(sb, method.StoredProcedureName);

			sb.Append("CREATE PROCEDURE [dbo].[").Append(method.StoredProcedureName).AppendLine("]");
			sb.AppendLine("\t(");

			PrimaryKeyTableProperty pkprop = _table.GetPrimaryKey();
			bool first = true;

			for ( int i = 0; i < pkprop.Properties.Count; i++ )
			{
				if ( !first )
				{
					sb.AppendLine(",");
				}
				else
				{
					first = false;
				}

				TableProperty prop = pkprop.Properties[i];

				sb.Append("\t");
				CreateParameter(sb, prop, false, false);
			}
			sb.AppendLine();

			sb.AppendLine("\t)");
			sb.AppendLine("AS");
			sb.Append("DELETE [").Append(_table.TableName).AppendLine("]");
			sb.AppendLine("WHERE");

			PrimaryKeyTableProperty pk = _table.GetPrimaryKey();

			first = true;
			for ( int i = 0; i < pk.Properties.Count; i++ )
			{
				if ( !first )
				{
					sb.AppendLine(" AND ");
				}
				else
				{
					first = false;
				}
				TableProperty prop = pk.Properties[i];

				sb.Append("\t[").Append(prop.ColumnName).Append("] = ").Append("@").Append(prop.ColumnName);
			}

			sb.AppendLine();
		}

		protected void GenerateDropStatement(StringBuilder sb, string procName)
		{
			sb.Append("IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = '").Append(procName).AppendLine("')");
			sb.AppendLine("\tBEGIN");
			sb.Append("\t\tDROP  Procedure [").Append(procName).AppendLine("]");
			sb.AppendLine("\tEND");
			sb.AppendLine("GO");
		}

		protected void CreateParameter(StringBuilder sb, TableProperty prop, bool output, bool nullable)
		{
			sb.Append("@").Append(prop.ColumnName).Append(" ").Append(prop.DataType.ToString().ToLower());
			switch ( prop.DataType )
			{
				case System.Data.SqlDbType.Binary:
				case System.Data.SqlDbType.Char:
				case System.Data.SqlDbType.NChar:
				case System.Data.SqlDbType.NVarChar:
				case System.Data.SqlDbType.VarChar:
					sb.Append("(").Append(prop.DataLength).Append(")");
					break;
			}

			if ( output )
				sb.Append(" OUTPUT");
			if ( nullable )
				sb.Append(" = NULL");

		}

		protected void CreateHeader(StringBuilder sb)
		{
			sb.Append('-', 50).AppendLine();
			sb.AppendLine();
			sb.AppendLine("SET ANSI_NULLS ON");
			sb.AppendLine("GO");
			sb.AppendLine("SET QUOTED_IDENTIFIER ON");
			sb.AppendLine("GO");
			sb.AppendLine();
		}
	}
}
